clear all
set more off

global rep "H:/Stata/Replication Materials"

* ====================================================
* CONTENTS
* 
* 1. Preparing the datasets for merging
* 2. Merging the datasets
* 3. Reshaping the data based on yearly place of residence
* 4. Recoding, renaming, and labeling variables
* 5. Creating additional variables
* ====================================================

* ====================================================
* 1. Preparing the datasets for merging
* ====================================================

// GBAPERSOONTAB
use "G:\Bevolking\GBAPERSOONTAB\2017\geconverteerde data\GBAPERSOON2017TABV1.DTA", clear
rename rinpersoons RINPERSOONS
rename rinpersoon RINPERSOON
drop gbageboortelandmoeder gbageboortelandvader gbaaantaloudersbuitenland ///
gbaherkomstgroepering gbageneratie gbageboortedag gbageboortedagmoeder 	  ///
gbageboortedagvader
save "$rep/temp_GBAPERSOON2017TABV1.DTA",replace

// VRLGBAGEBOORTEGEMEENTETAB
use "G:\Bevolking\VRLGBAGEBOORTEGEMEENTETAB\geconverteerde data\VRLGBAGEBOORTEGEMEENTE2017TABV1.DTA", clear
rename rinpersoons RINPERSOONS
rename rinpersoon RINPERSOON
save "$rep/temp_VRLGBAGEBOORTEGEMEENTE2017TABV1",replace

// GBAADRESOBJECTBUS
use "G:\Bevolking\GBAADRESOBJECTBUS\2017\geconverteerde data\GBAADRESOBJECT2017BUSV1.dta", clear
rename rinpersoons RINPERSOONS
rename rinpersoon RINPERSOON
save "$rep/temp_GBAADRESOBJECT2017BUSV1.DTA",replace

// VSLGWBTAB
use "H:/Stata/Data/VSLGWB2017TAB09V1.DTA", clear
keep SOORTOBJECTNUMMER RINOBJECTNUMMER gem2017
rename SOORTOBJECTNUMMER soortobjectnummer 
rename RINOBJECTNUMMER rinobjectnummer 

compress 
save "$rep/temp_VSLGWB2017TAB03V1.DTA",replace

// NIETVSLGWBTAB
use "G:\BouwenWonen\NIETVSLGWBTAB\geconverteerde data\NIETVSLGWB2020TABV1.dta", clear
keep soortobjectnummer rinobjectnummer gem2017
compress
save "$rep/NIETVSLGWB2020TABV1.dta",replace

// KINDOUDERTAB
use "H:\Stata\Data\KINDOUDER2017TABV1.DTA", clear
rename rinpersoons RINPERSOONS
rename rinpersoon RINPERSOON
sort RINPERSOON

bys RINPERSOONS RINPERSOON: keep if _n==1
save "$rep/temp_KINDOUDER2017TABV1.DTA", replace


* ====================================================
* 2. Merging the datasets
* ====================================================

use "$rep/temp_GBAPERSOON2017TABV1.DTA", clear

merge 1:1 RINPERSOONS RINPERSOON using "$rep/temp_VRLGBAGEBOORTEGEMEENTE2017TABV1"
drop _merge

merge 1:m RINPERSOONS RINPERSOON using "$rep/temp_GBAADRESOBJECT2017BUSV1.DTA"
drop _merge

merge m:1 soortobjectnummer rinobjectnummer using "$rep/temp_VSLGWB2017TAB03V1.DTA"
drop _merge

merge m:1 soortobjectnummer rinobjectnummer using "$rep/NIETVSLGWB2020TABV1.dta"
drop _merge

set varabbrev off
merge m:1 RINPERSOONS RINPERSOON using "$rep/temp_KINDOUDER2017TABV1.DTA"
drop _merge


* ====================================================
* 3. Renaming and reshaping the data based on yearly place of residence
* ====================================================


// renaming some variables
rename gbageboortejaar yob
rename gbageboortemaand mob
rename gbageboortejaarmoeder yob_mother
rename gbageboortejaarvader yob_father
rename gbageslacht female

// recoding values to missing
replace female = "." if female == "-"
replace gbageslachtmoeder = "." if gbageslachtmoeder == "-"
replace gbageslachtvader = "." if gbageslachtvader == "-"
replace RINPERSOONS = "." if RINPERSOONS == ""
replace RINPERSOON = "." if RINPERSOON == ""
replace gbageboorteland = "." if gbageboorteland == ""
replace yob = "." if yob == ""
replace mob = "." if mob == ""
replace yob_mother = "." if yob_mother == "----"
replace gbageboortemaandmoeder = "." if gbageboortemaandmoeder == "--"
replace yob_father = "." if yob_father == "----"
replace gbageboortemaandvader = "." if gbageboortemaandvader == "--"
replace vrlgbageboortegemeente = "." if vrlgbageboortegemeente == "" | vrlgbageboortegemeente == "----" | vrlgbageboortegemeente == "0000"
replace gbadatumaanvangadreshouding = "." if gbadatumaanvangadreshouding == ""
replace gbadatumeindeadreshouding = "." if gbadatumeindeadreshouding == ""
replace soortobjectnummer = "." if soortobjectnummer == ""
replace rinobjectnummer = "." if rinobjectnummer == ""
replace gem2017 = "." if gem2017 == "" | gem2017 == "----"

// recoding variables from string to numeric
destring female, replace
destring RINPERSOONS, replace
destring RINPERSOON, replace 
destring gbageboorteland, replace
destring yob, replace 
destring mob, replace 
destring gbageslachtmoeder, replace 
destring gbageslachtvader, replace 
destring yob_mother, replace 
destring gbageboortemaandmoeder, replace 
destring yob_father, replace 
destring gbageboortemaandvader, replace 
destring vrlgbageboortegemeente, replace 
destring gbadatumaanvangadreshouding, replace 
destring gbadatumeindeadreshouding, replace 
destring soortobjectnummer, replace 
destring rinobjectnummer, replace 
destring gem2017, replace 

// creating a variable which gives the most recent 1st of January in a year
gen year = .
replace year = 1995 if gbadatumaanvangadreshouding <=19950101
replace year = 1996 if gbadatumaanvangadreshouding >=19950102 & gbadatumaanvangadreshouding <=19960101
replace year = 1997 if gbadatumaanvangadreshouding >=19960102 & gbadatumaanvangadreshouding <=19970101
replace year = 1998 if gbadatumaanvangadreshouding >=19970102 & gbadatumaanvangadreshouding <=19980101
replace year = 1999 if gbadatumaanvangadreshouding >=19980102 & gbadatumaanvangadreshouding <=19990101
replace year = 2000 if gbadatumaanvangadreshouding >=19990102 & gbadatumaanvangadreshouding <=20000101
replace year = 2001 if gbadatumaanvangadreshouding >=20000102 & gbadatumaanvangadreshouding <=20010101
replace year = 2002 if gbadatumaanvangadreshouding >=20010102 & gbadatumaanvangadreshouding <=20020101
replace year = 2003 if gbadatumaanvangadreshouding >=20020102 & gbadatumaanvangadreshouding <=20030101
replace year = 2004 if gbadatumaanvangadreshouding >=20030102 & gbadatumaanvangadreshouding <=20040101
replace year = 2005 if gbadatumaanvangadreshouding >=20040102 & gbadatumaanvangadreshouding <=20050101
replace year = 2006 if gbadatumaanvangadreshouding >=20050102 & gbadatumaanvangadreshouding <=20060101
replace year = 2007 if gbadatumaanvangadreshouding >=20060102 & gbadatumaanvangadreshouding <=20070101
replace year = 2008 if gbadatumaanvangadreshouding >=20070102 & gbadatumaanvangadreshouding <=20080101
replace year = 2009 if gbadatumaanvangadreshouding >=20080102 & gbadatumaanvangadreshouding <=20090101
replace year = 2010 if gbadatumaanvangadreshouding >=20090102 & gbadatumaanvangadreshouding <=20100101
replace year = 2011 if gbadatumaanvangadreshouding >=20100102 & gbadatumaanvangadreshouding <=20110101
replace year = 2012 if gbadatumaanvangadreshouding >=20110102 & gbadatumaanvangadreshouding <=20120101
replace year = 2013 if gbadatumaanvangadreshouding >=20120102 & gbadatumaanvangadreshouding <=20130101
replace year = 2014 if gbadatumaanvangadreshouding >=20130102 & gbadatumaanvangadreshouding <=20140101
replace year = 2015 if gbadatumaanvangadreshouding >=20140102 & gbadatumaanvangadreshouding <=20150101
replace year = 2016 if gbadatumaanvangadreshouding >=20150102 & gbadatumaanvangadreshouding <=20160101
replace year = 2017 if gbadatumaanvangadreshouding >=20160102 & gbadatumaanvangadreshouding <=20170101

// this keeps only the first adress in a year because it is sorted on 
// start of occupying an adress > ADD rinpersoons
sort RINPERSOONS RINPERSOON year gbadatumaanvangadreshouding gbadatumeindeadreshouding
by RINPERSOONS RINPERSOON year: keep if _N


// creating the residence variable for all years
// 1995:
gen residence1995 = gem2017 if year == 1995
bysort RINPERSOONS RINPERSOON (residence1995): replace residence1995 =residence1995[1] if missing(residence1995)

// 1996:
gen residence1996 = gem2017 if year == 1996
bysort RINPERSOONS RINPERSOON (residence1996): replace residence1996 =residence1996[1] if missing(residence1996) 
bysort RINPERSOONS RINPERSOON (residence1996): replace residence1996 =residence1995[1] if missing(residence1996) 

// 1997:
gen residence1997 = gem2017 if year == 1997
bysort RINPERSOONS RINPERSOON (residence1997): replace residence1997 =residence1997[1] if missing(residence1997)
bysort RINPERSOONS RINPERSOON (residence1997): replace residence1997 =residence1996[1] if missing(residence1997) 

// 1998:
gen residence1998 = gem2017 if year == 1998
bysort RINPERSOONS RINPERSOON (residence1998): replace residence1998 =residence1998[1] if missing(residence1998)
bysort RINPERSOONS RINPERSOON (residence1998): replace residence1998 =residence1997[1] if missing(residence1998)

// 1999
gen residence1999 = gem2017 if year == 1999
bysort RINPERSOONS RINPERSOON (residence1999): replace residence1999 =residence1999[1] if missing(residence1999)
bysort RINPERSOONS RINPERSOON (residence1999): replace residence1999 =residence1998[1] if missing(residence1999)

// 2000
gen residence2000 = gem2017 if year == 2000
bysort RINPERSOONS RINPERSOON (residence2000): replace residence2000 =residence2000[1] if missing(residence2000)
bysort RINPERSOONS RINPERSOON (residence2000): replace residence2000 =residence1999[1] if missing(residence2000)

// 2001
gen residence2001 = gem2017 if year == 2001
bysort RINPERSOONS RINPERSOON (residence2001): replace residence2001 =residence2001[1] if missing(residence2001)
bysort RINPERSOONS RINPERSOON (residence2001): replace residence2001 =residence2000[1] if missing(residence2001)

// 2002
gen residence2002 = gem2017 if year == 2002
bysort RINPERSOONS RINPERSOON (residence2002): replace residence2002 =residence2002[1] if missing(residence2002)
bysort RINPERSOONS RINPERSOON (residence2002): replace residence2002 =residence2001[1] if missing(residence2002)

// 2003
gen residence2003 = gem2017 if year == 2003
bysort RINPERSOONS RINPERSOON (residence2003): replace residence2003 =residence2003[1] if missing(residence2003)
bysort RINPERSOONS RINPERSOON (residence2003): replace residence2003 =residence2002[1] if missing(residence2003)

// 2004
gen residence2004 = gem2017 if year == 2004
bysort RINPERSOONS RINPERSOON (residence2004): replace residence2004 =residence2004[1] if missing(residence2004)
bysort RINPERSOONS RINPERSOON (residence2004): replace residence2004 =residence2003[1] if missing(residence2004)

// 2005
gen residence2005 = gem2017 if year == 2005
bysort RINPERSOONS RINPERSOON (residence2005): replace residence2005 =residence2005[1] if missing(residence2005)
bysort RINPERSOONS RINPERSOON (residence2005): replace residence2005 =residence2004[1] if missing(residence2005)

// 2006
gen residence2006 = gem2017 if year == 2006
bysort RINPERSOONS RINPERSOON (residence2006): replace residence2006 =residence2006[1] if missing(residence2006)
bysort RINPERSOONS RINPERSOON (residence2006): replace residence2006 =residence2005[1] if missing(residence2006)

// 2007
gen residence2007 = gem2017 if year == 2007
bysort RINPERSOONS RINPERSOON (residence2007): replace residence2007 =residence2007[1] if missing(residence2007)
bysort RINPERSOONS RINPERSOON (residence2007): replace residence2007 =residence2006[1] if missing(residence2007)

// 2008
gen residence2008 = gem2017 if year == 2008
bysort RINPERSOONS RINPERSOON (residence2008): replace residence2008 =residence2008[1] if missing(residence2008)
bysort RINPERSOONS RINPERSOON (residence2008): replace residence2008 =residence2007[1] if missing(residence2008)

// 2009
gen residence2009 = gem2017 if year == 2009
bysort RINPERSOONS RINPERSOON (residence2009): replace residence2009 =residence2009[1] if missing(residence2009)
bysort RINPERSOONS RINPERSOON (residence2009): replace residence2009 =residence2008[1] if missing(residence2009)

// 2010
gen residence2010 = gem2017 if year == 2010
bysort RINPERSOONS RINPERSOON (residence2010): replace residence2010 =residence2010[1] if missing(residence2010)
bysort RINPERSOONS RINPERSOON (residence2010): replace residence2010 =residence2009[1] if missing(residence2010)

// 2011
gen residence2011 = gem2017 if year == 2011
bysort RINPERSOONS RINPERSOON (residence2011): replace residence2011 =residence2011[1] if missing(residence2011)
bysort RINPERSOONS RINPERSOON (residence2011): replace residence2011 =residence2010[1] if missing(residence2011)

// 2012
gen residence2012 = gem2017 if year == 2012
bysort RINPERSOONS RINPERSOON (residence2012): replace residence2012 =residence2012[1] if missing(residence2012)
bysort RINPERSOONS RINPERSOON (residence2012): replace residence2012 =residence2011[1] if missing(residence2012)

// 2013
gen residence2013 = gem2017 if year == 2013
bysort RINPERSOONS RINPERSOON (residence2013): replace residence2013 =residence2013[1] if missing(residence2013)
bysort RINPERSOONS RINPERSOON (residence2013): replace residence2013 =residence2012[1] if missing(residence2013)

// 2014
gen residence2014 = gem2017 if year == 2014
bysort RINPERSOONS RINPERSOON (residence2014): replace residence2014 =residence2014[1] if missing(residence2014)
bysort RINPERSOONS RINPERSOON (residence2014): replace residence2014 =residence2013[1] if missing(residence2014)

// 2015
gen residence2015 = gem2017 if year == 2015
bysort RINPERSOONS RINPERSOON (residence2015): replace residence2015 =residence2015[1] if missing(residence2015)
bysort RINPERSOONS RINPERSOON (residence2015): replace residence2015 =residence2014[1] if missing(residence2015)

// 2016
gen residence2016 = gem2017 if year == 2016
bysort RINPERSOONS RINPERSOON (residence2016): replace residence2016 =residence2016[1] if missing(residence2016)
bysort RINPERSOONS RINPERSOON (residence2016): replace residence2016 =residence2015[1] if missing(residence2016)

// 2017
gen residence2017 = gem2017 if year == 2017
bysort RINPERSOONS RINPERSOON (residence2017): replace residence2017 =residence2017[1] if missing(residence2017)
bysort RINPERSOONS RINPERSOON (residence2017): replace residence2017 =residence2016[1] if missing(residence2017)

sort RINPERSOONS RINPERSOON
bys RINPERSOONS RINPERSOON: keep if _n==1

drop residence1995 residence1996 residence1997 residence1999 residence2000 ///
residence2001 residence2004 residence2005 residence2007 residence2008 	   ///
residence2009 residence2011 residence2013 residence2014 residence2015      ///
residence2016

drop year gbadatumaanvangadreshouding gbadatumeindeadreshouding ///
soortobjectnummer rinobjectnummer gem2017

reshape long residence, i(RINPERSOON RINPERSOONS) j(year)


* ====================================================
* 4. Recoding and labeling variables
* ====================================================


// adding labels to variables
label var RINPERSOONS "Gives the source of RINPERSOON"
label var RINPERSOON  "Unique person identifier"
label var gbageboorteland "Country of birth"
label var female "Dummy variable for gender"
label var yob "Year of birth"
label var mob "Month of birth"
label var gbageslachtmoeder "Gender of mother"
label var gbageslachtvader "Gender of father"
label var yob_mother "Year of birth mother"
label var gbageboortemaandmoeder "Month of birth mother"
label var yob_father "Year of birth father"
label var gbageboortemaandvader "Month of birth father"


// adding value label for female
recode female (1=0) (2=1)
label define _female 0 "male" 1 "female"
label value female _female

// adding value label for gender of parents
label define _gender 1 "male" 2 "female"
label value gbageslachtmoeder _gender
label value gbageslachtvader _gender

// creating the value label for municipalities
label define _municipalities ///
0003  "Appingedam"	///
0005  "Bedum"	///
0007  "Bellingwedde"	///
0009  "Ten Boer"	///
0010  "Delfzijl"	///
0014  "Groningen"	///
0015  "Grootegast"	///
0017  "Haren"	///
0018  "Hoogezand-Sappemeer"	///
0022  "Leek"	///
0024  "Loppersum"	///
0025  "Marum"	///
0034  "Almere"	///
0037  "Stadskanaal"	///
0040  "Slochteren"	///
0047  "Veendam"	///
0048  "Vlagtwedde"	///
0050  "Zeewolde"	///
0053  "Winsum"	///
0056  "Zuidhorn"	///
0058  "Dongeradeel"	///
0059  "Achtkarspelen"	///
0060  "Ameland"	///
0063  "het Bildt"	///
0070  "Franekeradeel"	///
0072  "Harlingen"	///
0074  "Heerenveen"	///
0079  "Kollumerland en Nieuwkruisland"	///
0080  "Leeuwarden"	///
0081  "Leeuwarderadeel"	///
0085  "Ooststellingwerf"	///
0086  "Opsterland"	///
0088  "Schiermonnikoog"	///
0090  "Smallingerland"	///
0093  "Terschelling"	///
0096  "Vlieland"	///
0098  "Weststellingwerf"	///
0106  "Assen"	///
0109  "Coevorden"	///
0114  "Emmen"	///
0118  "Hoogeveen"	///
0119  "Meppel"	///
0140  "Littenseradiel"	///
0141  "Almelo"	///
0147  "Borne"	///
0148  "Dalfsen"	///
0150  "Deventer"	///
0153  "Enschede"	///
0158  "Haaksbergen"	///
0160  "Hardenberg"	///
0163  "Hellendoorn"	///
0164  "Hengelo"	///
0166  "Kampen"	///
0168  "Losser"	///
0171  "Noordoostpolder"	///
0173  "Oldenzaal"	///
0175  "Ommen"	///
0177  "Raalte"	///
0180  "Staphorst"	///
0183  "Tubbergen"	///
0184  "Urk"	///
0189  "Wierden"	///
0193  "Zwolle"	///
0196  "Rijnwaarden"	///
0197  "Aalten"	///
0200  "Apeldoorn"	///
0202  "Arnhem"	///
0203  "Barneveld"	///
0209  "Beuningen"	///
0213  "Brummen"	///
0214  "Buren"	///
0216  "Culemborg"	///
0221  "Doesburg"	///
0222  "Doetinchem"	///
0225  "Druten"	///
0226  "Duiven"	///
0228  "Ede"	///
0230  "Elburg"	///
0232  "Epe"	///
0233  "Ermelo"	///
0236  "Geldermalsen"	///
0243  "Harderwijk"	///
0244  "Hattem"	///
0246  "Heerde"	///
0252  "Heumen"	///
0262  "Lochem"	///
0263  "Maasdriel"	///
0267  "Nijkerk"	///
0268  "Nijmegen"	///
0269  "Oldebroek"	///
0273  "Putten"	///
0274  "Renkum"	///
0275  "Rheden"	///
0277  "Rozendaal"	///
0279  "Scherpenzeel"	///
0281  "Tiel"	///
0285  "Voorst"	///
0289  "Wageningen"	///
0293  "Westervoort"	///
0294  "Winterswijk"	///
0296  "Wijchen"	///
0297  "Zaltbommel"	///
0299  "Zevenaar"	///
0301  "Zutphen"	///
0302  "Nunspeet"	///
0303  "Dronten"	///
0304  "Neerijnen"	///
0307  "Amersfoort"	///
0308  "Baarn"	///
0310  "De Bilt"	///
0312  "Bunnik"	///
0313  "Bunschoten"	///
0317  "Eemnes"	///
0321  "Houten"	///
0327  "Leusden"	///
0331  "Lopik"	///
0335  "Montfoort"	///
0339  "Renswoude"	///
0340  "Rhenen"	///
0342  "Soest"	///
0344  "Utrecht"	///
0345  "Veenendaal"	///
0351  "Woudenberg"	///
0352  "Wijk bij Duurstede"	///
0353  "IJsselstein"	///
0355  "Zeist"	///
0356  "Nieuwegein"	///
0358  "Aalsmeer"	///
0361  "Alkmaar"	///
0362  "Amstelveen"	///
0363  "Amsterdam"	///
0370  "Beemster"	///
0373  "Bergen (NH.)"	///
0375  "Beverwijk"	///
0376  "Blaricum"	///
0377  "Bloemendaal"	///
0383  "Castricum"	///
0384  "Diemen"	///
0385  "Edam-Volendam"	///
0388  "Enkhuizen"	///
0392  "Haarlem"	///
0393  "Haarlemmerliede en Spaarnwoude"	///
0394  "Haarlemmermeer"	///
0396  "Heemskerk"	///
0397  "Heemstede"	///
0398  "Heerhugowaard"	///
0399  "Heiloo"	///
0400  "Den Helder"	///
0402  "Hilversum"	///
0405  "Hoorn"	///
0406  "Huizen"	///
0415  "Landsmeer"	///
0416  "Langedijk"	///
0417  "Laren"	///
0420  "Medemblik"	///
0431  "Oostzaan"	///
0432  "Opmeer"	///
0437  "Ouder-Amstel"	///
0439  "Purmerend"	///
0441  "Schagen"	///
0448  "Texel"	///
0450  "Uitgeest"	///
0451  "Uithoorn"	///
0453  "Velsen"	///
0457  "Weesp"	///
0473  "Zandvoort"	///
0479  "Zaanstad"	///
0482  "Alblasserdam"	///
0484  "Alphen aan den Rijn"	///
0489  "Barendrecht"	///
0498  "Drechterland"	///
0501  "Brielle"	///
0502  "Capelle aan den IJssel"	///
0503  "Delft"	///
0505  "Dordrecht"	///
0512  "Gorinchem"	///
0513  "Gouda"	///
0518  "'s-Gravenhage"	///
0523  "Hardinxveld-Giessendam"	///
0530  "Hellevoetsluis"	///
0531  "Hendrik-Ido-Ambacht"	///
0532  "Stede Broec"	///
0534  "Hillegom"	///
0537  "Katwijk"	///
0542  "Krimpen aan den IJssel"	///
0545  "Leerdam"	///
0546  "Leiden"	///
0547  "Leiderdorp"	///
0553  "Lisse"	///
0556  "Maassluis"	///
0569  "Nieuwkoop"	///
0575  "Noordwijk"	///
0576  "Noordwijkerhout"	///
0579  "Oegstgeest"	///
0584  "Oud-Beijerland"	///
0585  "Binnenmaas"	///
0588  "Korendijk"	///
0589  "Oudewater"	///
0590  "Papendrecht"	///
0597  "Ridderkerk"	///
0599  "Rotterdam"	///
0603  "Rijswijk"	///
0606  "Schiedam"	///
0610  "Sliedrecht"	///
0611  "Cromstrijen"	///
0613  "Albrandswaard"	///
0614  "Westvoorne"	///
0617  "Strijen"	///
0620  "Vianen"	///
0622  "Vlaardingen"	///
0626  "Voorschoten"	///
0627  "Waddinxveen"	///
0629  "Wassenaar"	///
0632  "Woerden"	///
0637  "Zoetermeer"	///
0638  "Zoeterwoude"	///
0642  "Zwijndrecht"	///
0654  "Borsele"	///
0664  "Goes"	///
0668  "West Maas en Waal"	///
0677  "Hulst"	///
0678  "Kapelle"	///
0687  "Middelburg"	///
0689  "Giessenlanden"	///
0703  "Reimerswaal"	///
0707  "Zederik"	///
0715  "Terneuzen"	///
0716  "Tholen"	///
0717  "Veere"	///
0718  "Vlissingen"	///
0733  "Lingewaal"	///
0736  "De Ronde Venen"	///
0737  "Tytsjerksteradiel"	///
0738  "Aalburg"	///
0743  "Asten"	///
0744  "Baarle-Nassau"	///
0748  "Bergen op Zoom"	///
0753  "Best"	///
0755  "Boekel"	///
0756  "Boxmeer"	///
0757  "Boxtel"	///
0758  "Breda"	///
0762  "Deurne"	///
0765  "Pekela"	///
0766  "Dongen"	///
0770  "Eersel"	///
0772  "Eindhoven"	///
0777  "Etten-Leur"	///
0779  "Geertruidenberg"	///
0784  "Gilze en Rijen"	///
0785  "Goirle"	///
0786  "Grave"	///
0788  "Haaren"	///
0794  "Helmond"	///
0796  "'s-Hertogenbosch"	///
0797  "Heusden"	///
0798  "Hilvarenbeek"	///
0809  "Loon op Zand"	///
0815  "Mill en Sint Hubert"	///
0820  "Nuenen, Gerwen en Nederwetten"	///
0823  "Oirschot"	///
0824  "Oisterwijk"	///
0826  "Oosterhout"	///
0828  "Oss"	///
0840  "Rucphen"	///
0845  "Sint-Michielsgestel"	///
0847  "Someren"	///
0848  "Son en Breugel"	///
0851  "Steenbergen"	///
0852  "Waterland"	///
0855  "Tilburg"	///
0856  "Uden"	///
0858  "Valkenswaard"	///
0861  "Veldhoven"	///
0865  "Vught"	///
0866  "Waalre"	///
0867  "Waalwijk"	///
0870  "Werkendam"	///
0873  "Woensdrecht"	///
0874  "Woudrichem"	///
0879  "Zundert"	///
0880  "Wormerland"	///
0881  "Onderbanken"	///
0882  "Landgraaf"	///
0888  "Beek"	///
0889  "Beesel"	///
0893  "Bergen (L.)"	///
0899  "Brunssum"	///
0907  "Gennep"	///
0917  "Heerlen"	///
0928  "Kerkrade"	///
0935  "Maastricht"	///
0938  "Meerssen"	///
0944  "Mook en Middelaar"	///
0946  "Nederweert"	///
0951  "Nuth"	///
0957  "Roermond"	///
0962  "Schinnen"	///
0965  "Simpelveld"	///
0971  "Stein"	///
0981  "Vaals"	///
0983  "Venlo"	///
0984  "Venray"	///
0986  "Voerendaal"	///
0988  "Weert"	///
0994  "Valkenburg aan de Geul"	///
0995  "Lelystad"	///
1507  "Horst aan de Maas"	///
1509  "Oude IJsselstreek"	///
1525  "Teylingen"	///
1581  "Utrechtse Heuvelrug"	///
1586  "Oost Gelre"	///
1598  "Koggenland"	///
1621  "Lansingerland"	///
1640  "Leudal"	///
1641  "Maasgouw"	///
1651  "Eemsmond"	///
1652  "Gemert-Bakel"	///
1655  "Halderberge"	///
1658  "Heeze-Leende"	///
1659  "Laarbeek"	///
1663  "De Marne"	///
1667  "Reusel-De Mierden"	///
1669  "Roerdalen"	///
1674  "Roosendaal"	///
1676  "Schouwen-Duiveland"	///
1680  "Aa en Hunze"	///
1681  "Borger-Odoorn"	///
1684  "Cuijk"	///
1685  "Landerd"	///
1690  "De Wolden"	///
1695  "Noord-Beveland"	///
1696  "Wijdemeren"	///
1699  "Noordenveld"	///
1700  "Twenterand"	///
1701  "Westerveld"	///
1702  "Sint Anthonis"	///
1705  "Lingewaard"	///
1706  "Cranendonck"	///
1708  "Steenwijkerland"	///
1709  "Moerdijk"	///
1711  "Echt-Susteren"	///
1714  "Sluis"	///
1719  "Drimmelen"	///
1721  "Bernheze"	///
1722  "Ferwerderadiel"	///
1723  "Alphen-Chaam"	///
1724  "Bergeijk"	///
1728  "Bladel"	///
1729  "Gulpen-Wittem"	///
1730  "Tynaarlo"	///
1731  "Midden-Drenthe"	///
1734  "Overbetuwe"	///
1735  "Hof van Twente"	///
1740  "Neder-Betuwe"	///
1742  "Rijssen-Holten"	///
1771  "Geldrop-Mierlo"	///
1773  "Olst-Wijhe"	///
1774  "Dinkelland"	///
1783  "Westland"	///
1842  "Midden-Delfland"	///
1859  "Berkelland"	///
1876  "Bronckhorst"	///
1883  "Sittard-Geleen"	///
1884  "Kaag en Braassem"	///
1891  "Dantumadiel"	///
1892  "Zuidplas"	///
1894  "Peel en Maas"	///
1895  "Oldambt"	///
1896  "Zwartewaterland"	///
1900  "Súdwest-Fryslân"	///
1901  "Bodegraven-Reeuwijk"	///
1903  "Eijsden-Margraten"	///
1904  "Stichtse Vecht"	///
1908  "Menameradiel"	///
1911  "Hollands Kroon"	///
1916  "Leidschendam-Voorburg"	///
1924  "Goeree-Overflakkee"	///
1926  "Pijnacker-Nootdorp"	///
1927  "Molenwaard"	///
1930  "Nissewaard"	///
1931  "Krimpenerwaard"	///
1940  "De Fryske Marren"	///
1942  "Gooise Meren"	///
1945  "Berg en Dal"	///
1948  "Meierijstad"	///
1955  "Montferland"	///
1987  "Menterwolde"	///
9001  "Bonaire"	///
9002  "Sint Eustatius"	///
9003  "Saba"	///
0998  "Buitenland"	///
9990  "Onbekend"

// attaching the municipality label to the municipality variables
label value vrlgbageboortegemeente  _municipalities
label value residence _municipalities

save "$rep/admin1", replace


* ====================================================
* 5. creating additional variables
* ====================================================

// linking municipalities to provinces
use "K:\Utilities\Code_Listings\Gebieden in Nederland\geconverteerde bestanden/GIN2017V1.dta", clear
// this dataset contains a list of muncipalities with corresponding pronvinces
// it does not contain any respondents, just all the municipalities.
keep gemeente provincie

 rename gemeente residence
rename provincie pro2017 // remove
 destring residence, replace
destring pro2017, replace
save "$rep\municipality_to_province.dta", replace

use "$rep/admin1", clear
 merge m:1 residence using "$rep/municipality_to_province.dta"

drop _merge


// labeling the provinces
label define _provinces ///
20 "Groningen" ///
21 "Friesland" ///
22 "Drenthe" ///
23 "Overijssel" ///
24 "Flevoland" ///
25 "Gelderland" ///
26 "Utrecht" ///
27 "Noord-Holland" ///
28 "Zuid-Holland" ///
29 "Zeeland" ///
30 "Noord-Brabant" ///
31 "Limburg"
label value pro2017 _provinces
save "$rep/microdata_no_additional_vars", replace

// matching birth municipalities to birth provinces:
import delimited "H:\Stata\Municipalities and provinces\Gemeentecodes\Municipalities_Provinces.csv", delimiter(";") clear
replace gemeentecode = substr(gemeentecode,3,4)
destring gemeentecode, replace
keep gemeentecode provincie
rename gemeentecode vrlgbageboortegemeente
save Municipalities_Provinces.dta, replace
use "$rep/microdata_no_additional_vars", clear
merge m:1 vrlgbageboortegemeente using "Municipalities_Provinces.dta"

rename provincie birthprovince
drop _merge

// region variable
gen region = 1 if ///
birthprovince == "Utrecht" | ///
birthprovince == "Noord-Holland" | ///
birthprovince == "Zuid-Holland" 
replace region = 0 if ///
birthprovince == "Groningen" | ///
birthprovince == "Friesland" | ///
birthprovince == "Drenthe" | ///
birthprovince == "Overijssel" | ///
birthprovince == "Flevoland" | ///
birthprovince == "Gelderland" | ///
birthprovince == "Zeeland" | ///
birthprovince == "Noord-Brabant" | ///
birthprovince == "Limburg"
label define _region 0 "nonwest" 1 "west"
label value region _region
label var region "The Netherlands dived in 2 regions: west and nonwest"

// region3 variable
gen region3 = 1 if ///
birthprovince == "Utrecht" | ///
birthprovince == "Noord-Holland" | ///
birthprovince == "Zuid-Holland" 
replace region3 = 2 if ///
birthprovince == "Groningen" | ///
birthprovince == "Friesland" | ///
birthprovince == "Drenthe" | ///
birthprovince == "Overijssel" | ///
birthprovince == "Flevoland" | ///
birthprovince == "Gelderland" 
replace region3 = 3 if ///
birthprovince == "Zeeland" | ///
birthprovince == "Noord-Brabant" | ///
birthprovince == "Limburg"
label define _region3 1 "west" 2 "northeast" 3 "south"
label value region3 _region3
label var region3 "The Netherlands dived in 3 regions: west, northeast and south"

// conti_urban: // no Breda?
gen conti_urban = 1 if  ///
vrlgbageboortegemeente == 363 | /// "Amsterdam" 
vrlgbageboortegemeente == 503 | /// "Delft"
vrlgbageboortegemeente == 518 | /// "'s-Gravenhage" (The Hague)
vrlgbageboortegemeente == 392 | /// "Haarlem"
vrlgbageboortegemeente == 546 | /// "Leiden"
vrlgbageboortegemeente == 599 | /// "Rotterdam"
vrlgbageboortegemeente == 344 | /// "Utrecht"
vrlgbageboortegemeente == 141 | /// "Almelo" 
vrlgbageboortegemeente == 200 | /// "Apeldoorn" 
vrlgbageboortegemeente == 150 | /// "Deventer" 
vrlgbageboortegemeente == 164 | /// "Hengelo"
vrlgbageboortegemeente == 268 | ///  "Nijmegen"
vrlgbageboortegemeente == 193 | /// "Zwolle"
vrlgbageboortegemeente == 14  | /// "Groningen"
vrlgbageboortegemeente == 748 | /// "Bergen op Zoom" 
vrlgbageboortegemeente == 772 | /// "Eindhoven"
vrlgbageboortegemeente == 794 | /// "Helmond"
vrlgbageboortegemeente == 796 | /// "'s-Hertogenbosch" 
vrlgbageboortegemeente == 928 | /// "Kerkrade"
vrlgbageboortegemeente == 935 | /// "Maastricht"
vrlgbageboortegemeente == 1674 | /// "Roosendaal" 
vrlgbageboortegemeente == 855 | /// "Tilburg"
vrlgbageboortegemeente == 917 | /// "Heerlen"
vrlgbageboortegemeente == 361 | /// "Alkmaar" 
vrlgbageboortegemeente == 307 | /// "Amersfoort" 
vrlgbageboortegemeente == 381 | /// "Bussum" 
vrlgbageboortegemeente == 505 | /// "Dordrecht" 
vrlgbageboortegemeente == 513 | /// "Gouda" 
vrlgbageboortegemeente == 471   //  "Zaandam" 
replace conti_urban = 0 if conti_urban != 1 & vrlgbageboortegemeente != .

// stein_urban
gen stein_urban = 1 if  ///
vrlgbageboortegemeente == 363 | /// "Amsterdam" 
vrlgbageboortegemeente == 503 | /// "Delft"
vrlgbageboortegemeente == 392 | /// "Haarlem"
vrlgbageboortegemeente == 546 | /// "Leiden"
vrlgbageboortegemeente == 599 | /// "Rotterdam"
vrlgbageboortegemeente == 518 | /// "'s-Gravenhage" (The Hague)
vrlgbageboortegemeente == 344 | /// "Utrecht"
vrlgbageboortegemeente == 758 | /// "Breda"
vrlgbageboortegemeente == 772 | /// "Eindhoven"
vrlgbageboortegemeente == 153 | /// "Enschede"
vrlgbageboortegemeente == 917 | /// "Heerlen"
vrlgbageboortegemeente == 14 |  /// "Groningen"
vrlgbageboortegemeente == 164 | /// "Hengelo"
vrlgbageboortegemeente == 80 |  /// "Leeuwarden"
vrlgbageboortegemeente == 935 | /// "Maastricht"
vrlgbageboortegemeente == 855 | /// "Tilburg"
vrlgbageboortegemeente == 193   //  "Zwolle"
replace stein_urban = 0 if stein_urban != 1 & vrlgbageboortegemeente != .

// all_urban_40
gen all_urban_40 = 1 if stein_urban == 1 | ///
vrlgbageboortegemeente == 307 | /// "Amersfoort" 
vrlgbageboortegemeente == 505 | /// "Dordrecht" 
vrlgbageboortegemeente == 402 | /// "Hilversum"
vrlgbageboortegemeente == 606 | /// "Schiedam"
vrlgbageboortegemeente == 622 | /// "Vlaardingen"
vrlgbageboortegemeente == 471 | /// "Zaandam"
vrlgbageboortegemeente == 114 | /// "Emmen"
vrlgbageboortegemeente == 228 | /// "Ede"
vrlgbageboortegemeente == 150 | /// "Deventer"
vrlgbageboortegemeente == 200 | /// "Apeldoorn"
vrlgbageboortegemeente == 796 | /// "'s-Hertogenbosch/ Den Bosch"
vrlgbageboortegemeente == 983   //  "Venlo"
replace all_urban_40 = 0 if all_urban_40 != 1 & vrlgbageboortegemeente != .

// all_urban_3040
gen all_urban_3040 = 1 if all_urban_40 == 1 | ///
vrlgbageboortegemeente == 361 | /// "Alkmaar"
vrlgbageboortegemeente == 381 | /// "Bussum"
vrlgbageboortegemeente == 513 | /// "Gouda"
vrlgbageboortegemeente == 394 | /// "Haarlemmermeer"
vrlgbageboortegemeente == 453 | /// "Velsen"
vrlgbageboortegemeente == 624 | /// "Voorburg"
vrlgbageboortegemeente == 355 | /// "Zeist"
vrlgbageboortegemeente == 141 | /// "Almelo"
vrlgbageboortegemeente == 794 | /// "Helmond"
vrlgbageboortegemeente == 928   //  "Kerkrade"
replace all_urban_3040 = 0 if all_urban_3040 != 1 & vrlgbageboortegemeente != .

// creating the market_garden variable / battle variable
gen market_garden = 1 if  ///
vrlgbageboortegemeente == 202 | /// "Arnhem"
vrlgbageboortegemeente == 268   //  "Nijmegen"
replace market_garden = 0 if market_garden != 1 & vrlgbageboortegemeente != .


save "$rep/admin_final", replace


rm "$rep/temp_GBAADRESOBJECT2017BUSV1.dta"
rm "$rep/temp_GBAPERSOON2017TABV1.dta"
rm "$rep/temp_KINDOUDER2017TABV1.dta"
rm "$rep/temp_VSLGWB2019TAB03V2.dta"
rm "$rep/temp_VRLGBAGEBOORTEGEMEENTE2017TABV1.dta"
rm "$rep/NIETVSLGWB2020TABV1.dta"
rm "$rep/municipality_to_province.dta"
rm "$rep/microdata_no_additional_vars.dta"
rm "$rep/admin1.dta"

* End.
